﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Xml.Serialization;
using System.Text;
using System.IO;

namespace AdHocReports
{
    [Serializable()]
    public class ReportDefinition
    {
        private string _ConnectionString = string.Empty;
        private List<string> _SelectColumns = new List<string>();
        private List<string> _GroupColumns = new List<string>();
        private List<string> _SumColumns = new List<string>();
        private List<string> _AvgColumns = new List<string>();
        private List<string> _MaxColumns = new List<string>();
        private List<string> _MinColumns = new List<string>();
        private List<string> _CountColumns = new List<string>();
        private List<string> _NumericFilters = new List<string>();
        private List<string> _DateTimeFilters = new List<string>();
        private List<string> _StringFilters = new List<string>();
        private List<string> _BooleanFilters = new List<string>();
        private List<Column> _DataTypes = new List<Column>();
        private string _View;
        private int _ReportID = -1;
        private string _ReportName = string.Empty;
        private string _SQL = string.Empty;

        public string ConnectionString
        {
            get
            {
                return _ConnectionString;
            }
            set
            {
                _ConnectionString = value;
            }
        }

        [XmlArray()]
        public List<string> SelectColumns
        {
            get
            {
                return _SelectColumns;
            }
        }

        [XmlArray()]
        public List<string> GroupColumns
        {
            get
            {
                return _GroupColumns;
            }
        }

        [XmlArray()]
        public List<string> SumColumns
        {
            get
            {
                return _SumColumns;
            }
        }

        [XmlArray()]
        public List<string> AvgColumns
        {
            get
            {
                return _AvgColumns;
            }
        }

        [XmlArray()]
        public List<string> MaxColumns
        {
            get
            {
                return _MaxColumns;
            }
        }

        [XmlArray()]
        public List<string> MinColumns
        {
            get
            {
                return _MinColumns;
            }
        }

        [XmlArray()]
        public List<string> CountColumns
        {
            get
            {
                return _CountColumns;
            }
        }

        [XmlArray()]
        public List<string> NumericFilters
        {
            get
            {
                return _NumericFilters;
            }
        }

        [XmlArray()]
        public List<string> DateTimeFilters
        {
            get
            {
                return _DateTimeFilters;
            }
        }

        [XmlArray()]
        public List<string> StringFilters
        {
            get
            {
                return _StringFilters;
            }
        }

        [XmlArray()]
        public List<string> BooleanFilters
        {
            get
            {
                return _BooleanFilters;
            }
        }

        [XmlArray()]
        public List<Column> DataTypes
        {
            get
            {
                return _DataTypes;
            }
        }

        public string View
        {
            get
            {
                return _View;
            }
            set
            {
                _View = value;
            }
        }

        public int ReportID
        {
            get
            {
                return _ReportID;
            }
            set
            {
                _ReportID = value;
            }
        }

        public string ReportName
        {
            get
            {
                return _ReportName;
            }
            set
            {
                _ReportName = value;
            }
        }

        public string SQL
        {
            get
            {
                if (_SQL.Length == 0)
                {
                    InitializeSql();
                }

                return _SQL;
            }
        }

        public ReportDefinition()
        {
            this.ResetDefintion();
        }

        public static ReportDefinition Current
        {
            get
            {
                ReportDefinition rd = HttpContext.Current.Session["rd"] as ReportDefinition;
                if (rd == null)
                {
                    rd = new ReportDefinition();
                    HttpContext.Current.Session.Add("rd", rd);
                }

                return rd;
            }
            set
            {
                if (HttpContext.Current.Session["rd"] == null)
                {
                    HttpContext.Current.Session.Add("rd", value);
                }
                else
                {
                    HttpContext.Current.Session["rd"] = value;
                }
            }
        }

        public string GetColumnDataType(string columnName)
        {
            for (int i = 0; i < _DataTypes.Count; i++)
            {
                if (_DataTypes[i].Name == columnName)
                {
                    return _DataTypes[i].Type;
                }
            }

            return string.Empty;
        }

        public void ResetDefintion()
        {
            this._ConnectionString = string.Empty;
            this._AvgColumns.Clear();
            this._BooleanFilters.Clear();
            this._CountColumns.Clear();
            this._DataTypes.Clear();
            this._DateTimeFilters.Clear();
            this._GroupColumns.Clear();
            this._MaxColumns.Clear();
            this._MinColumns.Clear();
            this._NumericFilters.Clear();
            this._SelectColumns.Clear();
            this._StringFilters.Clear();
            this._SumColumns.Clear();
            this._View = string.Empty;
            this._ReportID = -1;
            this._ReportName = string.Empty;
            this._SQL = string.Empty;
        }

        private void InitializeSql()
        {
            if (this._SelectColumns.Count == 0)
            {
                throw new InvalidOperationException("No columns to select.");
            }

            if (this._View.Length == 0)
            {
                throw new InvalidOperationException("No view has been specified.");
            }

            // Start building the SQL
            string sql = "SELECT \"" + string.Join("\", \"", this._SelectColumns.ToArray()) + "\" FROM \"" + this._View + "\"";

            // If there are numeric, date/time, string, or boolean filters
            if ((this._NumericFilters.Count > 0) || (this._DateTimeFilters.Count > 0) || (this._StringFilters.Count > 0) || (this._BooleanFilters.Count > 0))
            {
                // Start the WHERE clause
                sql += " WHERE ";
                sql += string.Join(" AND ", this._NumericFilters.ToArray());
                sql += string.Join(" AND ", this._DateTimeFilters.ToArray());
                sql += string.Join(" AND ", this._StringFilters.ToArray());
                sql += string.Join(" AND ", this._BooleanFilters.ToArray());
            }

            // If groups exist
            if (this._GroupColumns.Count > 0)
            {
                // Start the ORDER BY
                sql += " ORDER BY ";
                sql += string.Join(", ", this._GroupColumns.ToArray());
            }

            this._SQL = sql;
        }

        public override string ToString()
        {
            XmlSerializer ser = new XmlSerializer(this.GetType());
            StringBuilder sb = new StringBuilder();
            StringWriter sw = new StringWriter(sb);
            ser.Serialize(sw, this);

            return sw.ToString();
        }
    }
}
